跳到主要内容

MySQL 的 SQL 优化记录

前言，日常编写时经常会发现一条同样功能的 SQL 可能有不同的写法，那么怎么写才是最优的写法呢？这篇笔记就专门记录这方面的知识

join 的 on 和 where 的区别​

在使用 left join 时，on 和 where 条件的区别如下：

1、on 条件是在生成临时表时使用的条件，它不管 on 中的条件是否为真，都会返回左边表中的记录。 2、where 条件是在临时表生成好后，再对临时表进行过滤的条件。这时已经没有 left join 的含义（必须返回左边表的记录）了，条件不为真的就全部过滤掉。

所以：join on 后面的条件已经把结果过滤了一遍，而 where 则是把限制条件放到最后，执行最后一次查询前结果里值变多了，查询起来变慢了，效率自然变低了。

因此，有一个显而易见的 SQL 优化的方案是，当两张表的数据量比较大，又需要连接查询时，应该使用 FROM table1 JOIN table2 ON xxx 的语法，避免使用 FROM table1,table2 WHERE xxx 的语法，因为后者会在内存中先生成一张数据量比较大的笛卡尔积表，增加了内存的开销。

group by 的性能比 distinct 高​

注意：大表一般用 distinct 效率不高，大数据量的时候都禁止用 distinct，建议用 group by 解决重复问题。

SELECT DISTINCT columns FROM table_name WHERE where_conditions;

改成：

SELECT columns FROM table_name WHERE where_conditions GROUP BY columns;

对于 distinct 与 group by 的使用： 1、当对系统的性能高并数据量大时使用 group by 2、当对系统的性能不高时使用数据量少时两者皆可 3、尽量使用 group by

原因？

大量数据插入和查询的优化​

处理MySQL中的大量数据插入和查询时，以下是一些常见的优化策略：

  1. 批量插入：使用批量插入来减少插入操作的开销。通过将多个插入语句组合成一个批量插入语句，可以减少与数据库的通信次数，提高插入性能。可以使用多个值列表的 INSERT 语句或使用 LOAD DATA INFILE 语句来实现批量插入。

  2. 索引优化：为经常查询的列创建合适的索引，以提高查询性能。根据具体的查询需求和数据访问模式，选择合适的索引类型（如B-tree索引、哈希索引、全文索引等）。同时，避免创建过多的索引，因为过多的索引会增加插入和更新操作的开销。

  3. 分区表：如果数据量非常大，可以考虑将表按照某个列的范围进行分区。分区可以将数据分散存储在多个文件中，提高查询性能。例如，可以按照时间范围或地理位置进行分区。

  4. 垂直切分和水平切分：如果数据表非常大，可以考虑将表进行垂直切分或水平切分。垂直切分是将表拆分为多个具有相同主键但包含不同列的表，而水平切分是将表的行按照某个条件进行分割成多个表。切分可以将数据分散存储和查询，提高性能。

  5. 查询优化：使用合适的查询语句和优化技术来提高查询性能。避免使用不必要的子查询、全表扫描和大量的 JOIN 操作。使用 EXPLAIN 命令来分析查询计划，检查是否正确使用了索引。

  6. 数据库参数调优：根据具体的应用需求和硬件资源，调整 MySQL 的配置参数。例如，调整缓冲区大小、连接数限制、并发线程数等参数，以提高性能和吞吐量。

  7. 使用缓存：考虑使用缓存技术，如使用内存缓存（如 Redis）或查询缓存（如 MySQL 的查询缓存）来缓存经常查询的数据或查询结果，减少数据库的访问压力。

  8. 建立合理的数据模型：设计良好的数据模型可以提高数据查询的效率。合理划分表和字段，避免冗余数据和无效字段的存在。

  9. 数据库复制和分布式架构：对于大规模的数据处理和高并发访问，可以考虑使用数据库复制和分布式架构来实现负载均衡和数据分布，以提高性能和可扩展性。

以上是一些常见的优化策略，具体的优化方案应根据具体的业务需求、数据特点和硬件环境来选择和实施。在进行优化时，建议通过性能测试和监控来评估和验证优化效果，以确保所采取的优化措施真正能够提升数据库的性能。

Reference​